﻿Imports System.Data
Imports System.Data.Odbc
Imports System.Text
Imports System.Collections.Generic
Imports WSEntidades
Imports WSFactory

Public Class AbonosDAL
    Public Function registraAbono(ByVal pAbonos As AbonosBE) As Integer
        Dim instrSql As String
        Dim oInsParm(0 To 11) As OdbcParameter
        Try

            instrSql = "INSERT INTO ""Abonos""(""Almacen"", ""AnioAprob"", ""Banco"", ""Cadena"", ""Concepto"", ""CondicionesP"", " & _
                                        """Descuentos"", ""DescuentosAdic"", ""Enviada"", ""FCancelFol"", ""Fecha"", ""FEmisionFol"") " & _
                                        "VALUES (?,?,?,?,?,?,?,?,?,?,?,?);"

            oInsParm(0) = New OdbcParameter("", pAbonos.Almacen)
            oInsParm(1) = New OdbcParameter("", pAbonos.AnioAprob)
            oInsParm(2) = New OdbcParameter("", pAbonos.Banco)
            oInsParm(3) = New OdbcParameter("", pAbonos.Cadena)
            oInsParm(4) = New OdbcParameter("", pAbonos.Concepto)
            oInsParm(5) = New OdbcParameter("", pAbonos.CondicionesP)
            oInsParm(6) = New OdbcParameter("", pAbonos.Descuentos)
            oInsParm(7) = New OdbcParameter("", pAbonos.DescuentosAdic)
            oInsParm(8) = New OdbcParameter("", pAbonos.Enviada)
            oInsParm(9) = New OdbcParameter("", pAbonos.FCancelFol)
            oInsParm(10) = New OdbcParameter("", pAbonos.Fecha)
            oInsParm(11) = New OdbcParameter("", pAbonos.FEmisionFol)

            'Crea el objeto connection
            Using myConn As OdbcConnection = New OdbcConnection(My.Settings.cs)
                Using myComm As New OdbcCommand(instrSql, myConn)
                    myComm.CommandType = CommandType.Text
                    myComm.Parameters.AddRange(oInsParm)
                    'If (myConn.State = ConnectionState.Closed) Then
                    myConn.Open()
                    'End If
                    myComm.ExecuteNonQuery()
                    If (myComm.ExecuteNonQuery() > 0) Then
                        Return True
                    Else
                        Return False
                    End If
                End Using
            End Using
        Catch ex As Exception
            Throw
        Finally
            oInsParm = Nothing
            instrSql = Nothing
        End Try
    End Function

    'Public Function getAbonos(ByVal pIdEmpr As Integer, ByVal pStrAlmacen As String, ByVal pStrConcepto As String) As List(Of AbonosBE)
    '    Dim instrSql As String
    '    Dim aboLs As List(Of AbonosBE) = New List(Of AbonosBE)
    '    Try
    '        instrSql = "SELECT ""Id"", ""idEmpresa"", ""Almacen"", ""AnioAprob"", ""Banco"", ""Cadena"", ""Concepto"", ""CondicionesP"" " & _
    '                                 """Descuentos"", ""DescuentosAdic"", ""Enviada"", ""FCancelFol"", ""Fecha"", ""FEmisionFol"" " & _
    '                                "FROM ""Abonos"" " & _
    '                                "WHERE ""IdEmpresa"" = ? " & _
    '                                    "AND ""Almacen"" LIKE '%" & pStrAlmacen & "%' " & _
    '                                    "AND ""Concepto"" LIKE '%" & pStrConcepto & "%';"

    '        Using myConn As OdbcConnection = New OdbcConnection(My.Settings.cs)
    '            Using myComm As OdbcCommand = New OdbcCommand(instrSql, myConn)
    '                myComm.CommandType = CommandType.Text
    '                myComm.Parameters.Add(New OdbcParameter("", pIdEmpr))
    '                myConn.Open()
    '                Using myDR As OdbcDataReader = myComm.ExecuteReader()
    '                    While myDR.Read()
    '                        Dim abonos As AbonosBE = abonosFact.Build(myDR)
    '                        If (Not abonos Is Nothing) Then
    '                            aboLs.Add(abonos)
    '                        End If
    '                        abonos = Nothing
    '                    End While
    '                End Using
    '            End Using
    '        End Using
    '        Return aboLs
    '    Catch ex As Exception
    '        Throw
    '    Finally
    '        aboLs = Nothing
    '        instrSql = Nothing
    '    End Try
    'End Function

    'Public Function getAbonosByEmpr(ByVal pIdEmpr As Integer) As List(Of AbonosBE)
    '    Dim instrSql As String
    '    Dim aboLs As List(Of AbonosBE) = New List(Of AbonosBE)
    '    Try
    '        instrSql = "SELECT ""Id"", ""idEmpresa"", ""Almacen"", ""AnioAprob"", ""Banco"", ""Cadena"", ""Concepto"", ""CondicionesP"" " & _
    '                                 """Descuentos"", ""DescuentosAdic"", ""Enviada"", ""FCancelFol"", ""Fecha"", ""FEmisionFol"" " & _
    '                                "FROM ""Abonos"" " & _
    '                                "WHERE ""IdEmpresa"" = ?;"

    '        Using myConn As OdbcConnection = New OdbcConnection(My.Settings.cs)
    '            Using myComm As OdbcCommand = New OdbcCommand(instrSql, myConn)
    '                myComm.CommandType = CommandType.Text
    '                myComm.Parameters.Add(New OdbcParameter("", pIdEmpr))
    '                myConn.Open()
    '                Using myDR As OdbcDataReader = myComm.ExecuteReader()
    '                    While myDR.Read()
    '                        Dim abonos As AbonosBE = abonosFact.Build(myDR)
    '                        If (Not abonos Is Nothing) Then
    '                            aboLs.Add(abonos)
    '                        End If
    '                        abonos = Nothing
    '                    End While
    '                End Using
    '            End Using
    '        End Using
    '        Return aboLs

    '    Catch ex As Exception
    '        Throw 
    '    Finally
    '        aboLs = Nothing
    '        instrSql = Nothing
    '    End Try
    'End Function

    'Public Function getAbonoById(ByVal pId As Integer, ByVal pIdEmpr As Integer) As AbonosBE
    '    Dim instrSql As String
    '    Dim tmpAbo As AbonosBE
    '    Try
    '        instrSql = "SELECT ""Id"", ""idEmpresa"", ""Almacen"", ""AnioAprob"", ""Banco"", ""Cadena"", ""Concepto"", ""CondicionesP"" " & _
    '                                 """Descuentos"", ""DescuentosAdic"", ""Enviada"", ""FCancelFol"", ""Fecha"", ""FEmisionFol"" " & _
    '                                "FROM ""Abonos"" " & _
    '                                "WHERE ""Id"" = ? " & _
    '                                    "AND ""IdEmpresa"" = ?;"

    '        Using myConn As OdbcConnection = New OdbcConnection(My.Settings.cs)
    '            Using myComm As New OdbcCommand(instrSql, myConn)
    '                myComm.CommandType = CommandType.Text
    '                myComm.Parameters.Add(New OdbcParameter("", pId))
    '                myComm.Parameters.Add(New OdbcParameter("", pIdEmpr))
    '                myConn.Open()
    '                Using myDR As OdbcDataReader = myComm.ExecuteReader()
    '                    If (myDR.HasRows()) Then
    '                        myDR.Read()
    '                        tmpAbo = abonosFact.Build(myDR)
    '                    Else
    '                        tmpAbo = Nothing
    '                    End If
    '                End Using
    '            End Using
    '        End Using
    '        Return tmpAbo

    '    Catch ex As Exception
    '        Throw 
    '    Finally
    '        tmpAbo = Nothing
    '        instrSql = ""
    '    End Try
    'End Function

    Public Function existeAbono(ByVal pAbono As Integer, ByVal pIdEmpr As Integer) As Boolean
        Dim existe As Boolean = False
        Dim instrSql As String
        Dim oSelParm(0 To 1) As OdbcParameter
        Try

            instrSql = "SELECT COUNT(*) " & _
                                    "FROM ""Abonos"" " & _
                                    "WHERE ""IdEmpresa"" = ? " & _
                                        "AND ""Id"" = ?;"

            oSelParm(0) = New OdbcParameter("", pIdEmpr)
            oSelParm(1) = New OdbcParameter("", pAbono)
            Dim count As Object = db.ExecScalar(instrSql, CommandType.Text, oSelParm, My.Settings.cs)
            If (Not count Is Nothing) Then
                If (CShort(count) > 0) Then
                    existe = True
                Else
                    existe = False
                End If
            Else
                existe = False
            End If
            Return existe
            
        Catch ex As Exception
            Throw 
        Finally
            oSelParm = Nothing
            instrSql = Nothing
            existe = Nothing
        End Try
    End Function

    'Public Function getAbonoByAbono(ByVal pAbono As Integer, ByVal pIdEmpr As Integer) As AbonosBE
    '    Dim tmpAbo As AbonosBE
    '    Dim instrSql As String
    '    Try
    '        instrSql = "SELECT ""Id"", ""idEmpresa"", ""Almacen"", ""AnioAprob"", ""Banco"", ""Cadena"", ""Concepto"", ""CondicionesP"" " & _
    '                                 """Descuentos"", ""DescuentosAdic"", ""Enviada"", ""FCancelFol"", ""Fecha"", ""FEmisionFol"" " & _
    '                                "FROM ""Abonos"" " & _
    '                                "WHERE ""idEmpresa"" = ? " & _
    '                                    "AND ""id"" = ?;"

    '        Using myConn As OdbcConnection = New OdbcConnection(My.Settings.cs)
    '            Using myComm As New OdbcCommand(instrSql, myConn)
    '                myComm.CommandType = CommandType.Text
    '                myComm.Parameters.Add(New OdbcParameter("", pIdEmpr))
    '                myComm.Parameters.Add(New OdbcParameter("", pAbono))
    '                myConn.Open()
    '                Using myDR As OdbcDataReader = myComm.ExecuteReader()
    '                    If (myDR.HasRows()) Then
    '                        myDR.Read()
    '                        tmpAbo = abonosFact.Build(myDR)
    '                    Else
    '                        tmpAbo = Nothing
    '                    End If
    '                End Using
    '            End Using
    '        End Using
    '        Return tmpAbo

    '    Catch ex As Exception
    '        Throw 
    '    Finally
    '        tmpAbo = Nothing
    '        instrSql = ""
    '    End Try
    'End Function
End Class

